package com.situ.personal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.situ.personal.entity.Department;
import com.situ.personal.entity.User;
import com.situ.personal.util.JDBCUtil;

public class DepartmentDao {

	public List<Department> findAll() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Department > list = new ArrayList() ;
		
		try {
			connection=JDBCUtil.getConnection();
			String sql = "select * from department";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("Dao/findAll()/24Line"+preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				Department department = new Department(id, name);
				list.add(department);
			}
		} catch (Exception e) {
			
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		
		return list;
	}

	public int getTotalPage() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count= 0;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select count(*) from department";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("DepartmentDao/getTotalPage 51行："+preparedStatement); //测试   打印SQL语句
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next() ) {
				count = resultSet.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		System.out.println("count："+count);
		return count;
	}

	public List<Department> findByPage(int offset, int pageSize) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Department> list = new ArrayList<Department>();
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name from department limit ?,?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println("DepartmentDao/findByPage 65行："+preparedStatement); //测试   打印SQL语句
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next() ) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				Department department = new Department(id, name);
				System.out.println(department);
				list.add(department);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		System.out.println(list);
		return list;
	}

	public boolean deleteById(int id) {
		boolean isDelete =false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from department where id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			System.out.println("DepartmentDao/deleteById/104Lines"+preparedStatement);
			int count = preparedStatement.executeUpdate();
			if (count>0) {
				isDelete = true;
				System.out.println("DepartmentDao_SQL删除成功");
			}else {
				System.out.println("DepartmentDao_SQL删除失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(null, preparedStatement, connection);
		}
		return isDelete;
	}

	public boolean insertDepartment(String departmentName) {
		boolean isInsert = false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO department(`name`) VALUES(?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, departmentName);
			System.out.println("DepartmentDao/insertDepartment/129Lines"+preparedStatement); //SQL打印测试
			int count = preparedStatement.executeUpdate();
			if (count>0) {
				isInsert = true;
				System.out.println("DepartmentDao_SQL添加成功");
			}else {
				System.out.println("DepartmentDao_SQL添加失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(null, preparedStatement, connection);
		}

		return isInsert;
	}

	public Department selectById(int departmentId) {
		Department department = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;	
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select * from department where id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, departmentId);
			System.out.println("DepartmentDao/selectByIdt/158Lines"+preparedStatement); //SQL 打印测试
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				department = new Department(id, name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return department;
	}

	public boolean updateDepartmentInfo(int id, String name) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean isSuccess = false;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "update department set name=? where id=?";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("修改功能SQL/UserDao129Line："+preparedStatement);
			preparedStatement.setInt(2,id);
			preparedStatement.setString(1,name);
			System.out.println("DepartmentDao/updateDepartmentInfo/185Lines"+preparedStatement); //SQL 打印测试
			int count = preparedStatement.executeUpdate();
			if (count>0) {
				isSuccess = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return isSuccess;
	}

}
